import pymysql
import pandas as pd
from algorithm import *


def get_search_record(username):
    con = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='root',
        db='compkey',
        charset='utf8'
    )

    try:

        cur = con.cursor(cursor=pymysql.cursors.DictCursor)

        #     读取表
        get_userid_sql = "select userid,account from user where account = " + "'" + username + "'"
        cur.execute(get_userid_sql)
        get_userid_df = pd.DataFrame(cur.fetchall())

        userid = get_userid_df['userid'][0]

        get_record_sql = "select seed_word, record_time from record where userid = " + str(userid)
        cur.execute(get_record_sql)
        get_record_df = pd.DataFrame(cur.fetchall())

        record_list = get_record_df['seed_word']
        record_time_list = get_record_df['record_time']
        # print(type(record_list))
        outcome_list = []
        for i in range(0, len(record_list)):
            item = (record_list[i], str(record_time_list[i]))
            outcome_list.append(item)

        return outcome_list

    except Exception as e:
        raise e
    finally:
        con.close()


def statistics(record_list):
    counts = {}
    for word in record_list:
        if len(word) > 1 and word != '\n' and word != '\t':
            counts[word] = counts.get(word, 0) + 1

    return counts


def get_record(username):
    con = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='root',
        db='compkey',
        charset='utf8'
    )

    try:
        cur = con.cursor(cursor=pymysql.cursors.DictCursor)

        #     读取表
        get_userid_sql = "select userid,account from user where account = " + "'" + username + "'"
        cur.execute(get_userid_sql)
        get_userid_df = pd.DataFrame(cur.fetchall())

        userid = get_userid_df['userid'][0]
        print(type(userid))

        get_record_sql = "select seed_word from record where userid = " + str(userid)
        cur.execute(get_record_sql)
        get_record_df = pd.DataFrame(cur.fetchall())
        record_list = get_record_df['seed_word']
        print(type(record_list))
        record_outcome = []
        for record in record_list:
            record_outcome.append(record)

        return record_outcome

    except Exception as e:
        raise e
    finally:
        con.close()


def recommend_list(user):
    record_list = get_record(user)
    counts = statistics(record_list)
    sum_numer = 0
    print(counts)
    for count in counts:
        sum_numer += counts[count]

    comp_word_outcome = {}
    record_word = {}
    weight_dict_outcome = {}

    for count in counts:
        record_word[count] = []
        comp_words, midwords_info_list, weight_dict = compKey(count)
        weight_dict_outcome[count] = counts[count] / sum_numer
        print(count)
        for comp_word in comp_words:
            word = comp_word[0]
            if not (word in comp_word_outcome.keys()):
                comp_word_outcome[word] = 0
            record_word[count].append(word)
            comp_word_outcome[word] += counts[count] / sum_numer * comp_word[1]

    comp_word_outcome = sorted(comp_word_outcome.items(), key=lambda d: d[1], reverse=True)

    return comp_word_outcome[0:10], record_word, weight_dict_outcome


if __name__ == '__main__':
    serach_list = get_search_record('dqxlzlz')
    print(serach_list)
